#
# DCOracle2 python bindings
#
# Copyright (c) 2000, Digital Creations, Fredericksburg, VA, USA.  
# All rights reserved.
#
#  Redistribution and use in source and binary forms, with or without
#  modification, are permitted provided that the following conditions are
#  met:
#  
#    o Redistributions of source code must retain the above copyright
#      notice, this list of conditions, and the disclaimer that follows.
#  
#    o Redistributions in binary form must reproduce the above copyright
#      notice, this list of conditions, and the following disclaimer in
#      the documentation and/or other materials provided with the
#      distribution.
#  
#    o Neither the name of Digital Creations nor the names of its
#      contributors may be used to endorse or promote products derived
#      from this software without specific prior written permission.
#  
#  
#  THIS SOFTWARE IS PROVIDED BY DIGITAL CREATIONS AND CONTRIBUTORS *AS
#  IS* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
#  TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
#  PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL DIGITAL
#  CREATIONS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
#  INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
#  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
#  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
#  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
#  TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
#  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
#  DAMAGE.
# 
# $Id: DCOracle2.py,v 1.103 2003/10/30 15:30:32 chrisw Exp $

#
# Stored Procedure Narrative Documenation
#
# Stored procedures are some of most difficult thing to get working right.
# Normal statements will undergo type conversion by Oracle for input parameters
# and offer a description of the resultant output columns after the execute
# takes place for conversion back to Python.
#
# Stored procedures, on the other hand, modify variables defined by the
# calling environment.  Thus, those variables must be set up in advance,
# and must align with what the procedure is defined for.  It is possible
# to still have Oracle do type conversion on the variables (dco2 does
# use strings for numbers at the time of this note) but a lot more must
# be set up in advance for the process to work (e.g. binding arrays of
# a structure, returning cursors, etc.)
#
# The original DCOracle left behind a notion of the "procedure" object
# on a cursor.  This procedure object had as children all procedures
# available to the cursor it was invoked on.  Thus, you can use
# a notion of cursor.procedures.SCHEMA.PACKAGE.PROCEDURE(params) to invoke
# a particular stored procedure.
#
# DCOracle2 continues this, although it leads to some convoluted looking
# code (particularly the getattr on a procedure).
#
# The database connection can do a schema describe of any object in Oracle.
# This describe() function returns back a dictionary (of lists and more)
# which represent the results of the OCIDescribeAny call.  The mapproc
# method on the connection uses describe and the method collapsedesc to
# "collapse" the description down into a more terse form.
#
# The utility functions decodedesc and decodecdesc will decode the
# uncollapsed and collapsed descriptions, for diagnostic purposes.
#
# When mapproc() on a database object is called, it will save the description
# of the mapped object into the _procdesc attribute on the connection. This
# avoids calling OCIDescribeAny again for any schema object.
#
# When a cursor object is asked for its procedure or procedures attribute,
# the cursor __getattr__ code will create a new top-level procedure object,
# if it does not exist, and then save this object into the cursor's dictionary
# so that the __getattr__ code is not called again.
#
# The procedure object's __getattr__ does most of the heavy permutation,
# creating new procedure objects representing each segment of the schema
# name being traversed, and associating the invoking cursor with that object.
# Each time the procedure's __getattr__ makes a new attribute (for dynamic
# discovery) it is added to the procedure's dict so the getattr hook does
# not get invoked again.  
#
# The description of the object being traversed is saved in the procedure
# object so it does not have to be fetched again.  The decoded description
# is saved in the procedure's __doc__ string as human-readable text.
#
# The cursor's callproc method (the API 2.0 compliant way of invoking a
# stored procedure) calls the cursor findproc() method, which firsts checks
# to see if a known procedure is found by looking in the cursor's _mapproc
# attribute.  If not, it breaks the name up by splitting on periods, and
# tries to getattr() each successive element from the procedure object,
# starting with the base procedure object.  Once it finds a procedure object
# it saves it in its _mapproc attribute so it does not have to do the
# traversal again.
#


import dco2
import sys
import types
import time
from string import split, join, upper, find
from version import VERSION

import thread
import logging
logger = logging.getLogger('DCOracle2 - DCOracle2.py')

#
# PYTHON VERSION NOTES
#
# This code should be compatible with Python 1.5.2 and higher.  When making
# version-specific changes to the code, all attempts should be made to
# attempt to prevent this from harming older versions of the Python interpeter.
#

version = VERSION + "$Revision: 1.103 $" [11:-2]+" (dco2: %s %s)" % ( 
    dco2.__version__, dco2.buildDefs)

apilevel = '2.0'

threadsafety = 3    # Sort of; obviously two threads manipulating the
                    # same cursor needs to be synchronized!
                
paramstyle = 'numeric' # We do 'named' too

DEFAULTPROCSIZE=256
DEFAULTPROCCOUNT=256

NativeNumeric = find(version,'NATIVENUMERIC=1') > 0

DateConversion = None 

# This is the list of operations where the result row count is good aftewards
# note that SELECT doesn't return a rowcount
RowCountResults = (2, 3, 4)

# For the next() idiom which is Python 2.2, there is no StopIteration in
# earlier pythons

try:
    StopIteration
except NameError:
    class StopIteration(Exception):
        pass

def registerDateConversion(x):
    global DateConversion
    DateConversion = x

def connect(dsn=None, user=None, password=None, database=None):

    """Connect to Oracle.  The default argument is the dsn 'connection string'
    e.g. connect("scott/tiger@orac") but each parameter may also be
    specified, e.g. connect(user="scott", password="tiger", database="orac")."""

    # IN case we're called as an unbound method from ZOracleDA
    if hasattr(dsn,'connection_string'):
        dsn = getattr(dsn,'connection_string')
        user = None

    if dsn is not None:
        try:
            userpass, dbname = split(dsn,"@")
        except ValueError:
            userpass = dsn
            dbname = ""
    else:
        userpass = dsn
        dbname = ""
     
    if userpass is not None:
        try:
            u, p = split(userpass,"/")
        except ValueError:  
            u = dsn
            p = ""
    else:
        u = dsn
        p = ""

    if dsn is None: u = ""
    if user is not None: u = user
    if password is not None: p = password
    if database is not None: dbname = database

    db = dco2.connect(u, p, dbname)
    conn= connection(db)
    #print "Created new DCO2 connection %s" % conn
    logger.debug( "Created new DCO2 connection %s" % (conn) )
    return conn

Connect=connect     # For DCO 1 compatibility

# NONAPI
def traceback(format=0):
    """Generate a trace table list, either raw or formatted with newlines."""
    t = dco2.Traceback()
    s = t
    if format is not 0:
        s = ""
        for r in t:
            s = "%s%s\n" % (s, r)

    return s

# NONAPI
TypeTable = dco2.TypeTable

# NONAPI
def leakcheck():
    """Look for leaks in dco2 objects"""
    refs = {}
    for v in dir(dco2):
        if v[-4:] == "Type":
            refs[v] = sys.getrefcount(v)

    return refs

# NONAPI
def Type(n):
    """Convert a number or named type (e.g. SQLT_CHR) into an external
    datatype name."""

    d={ 1:  'VARCHAR2',     # Really VARCHAR2
        2:  'NUMBER',
        3:  'INTEGER',
        4:  'FLOAT',
        5:  'STRING',     # Really, its a string
        6:  'VARNUM',
        8:  'LONG',
        9:  'VARCHAR',
        11: 'ROWID',
        12: 'DATE',
        15: 'VARRAW',
        23: 'RAW',
        24: 'LONG RAW',
        68: 'INTEGER',
        94: 'LONG VARCHAR',
        95: 'LONG VARRAW',
        96: 'CHAR',      # Really CHAR
        97: 'CHARZ',      # Really CHARZ
        102: 'REF CURSOR', # SQLT_CUR
        104: 'ROWID',
        108: 'TYPE',
        110: 'REF',
        112: 'CLOB',
        113: 'BLOB',
        114: 'FLOB',
        155: 'STRING',    # Really STRING
        156: 'DATE',
        250: 'RECORD',      # strange documentation...
        251: 'TABLE',       # from the net points out...
        252: 'BOOLEAN'      # these typecodes (not official!)
    }

    if  type(n) == types.StringType:
        n = TypeTable(n)
    if d.has_key(n):
        n = d[n]
    elif type(n) == types.IntType:
       n = TypeTable(n)

    return n

#
# connection objects
#
#
# Connections track the underlying dco2 connection, and also have implicit
# cursors associated with them.
#
# Attributes used:
#
#   _db         Database connection from dco2 connect()
#   _cursor     Internal cursor to use connection object as a cursor
#   _procdesc   A dictionary of dco2.describe() data for stored procedures
#


class connection:

    _db = None

    def __init__(self, db):
        d = self.__dict__   
        d['_db'] = db       # Work around __setattr__ hook
        #d['_cursor'] = self.cursor()
        d['_procdesc'] = {}

    def close(self):
        if self._cursor:
            self._cursor.close()
        self._db.close()
        d = self.__dict__   # Work around __setattr__ hook
        del d['_cursor'] 
        d['_db'] = None
        d['_procdesc'] = {}

    # NONAPI
    def isOpen(self):
        "Is our internal connection around?"
        return self._db is not None

    def commit(self,twophase=0):        
        logger.debug( 'commit, twophase: %s, txn.%d' % (twophase, thread.get_ident()) )
        if self._db is None:
            logger.error( 'the database connection is closed' )
            raise InterfaceError, "The database connection is closed"            
        if twophase != 0: 
            self._db.commit(2)
        else:
            self._db.commit()

    def rollback(self):
        logger.debug( 'rollback' )
        if self._db is None:
            logger.error( 'The database connection is closed' )
            raise InterfaceError, "The database connection is closed"
        try:
            self._db.rollback()
        except dco2.DatabaseError:
            logger.error( 'rollback - DatabaseError - closing the connection' )
            self.close()
            logger.debug( 'rollback - DatabaseError - connection closed' )

    def cursor(self):
        if self._db is None:
            raise InterfaceError, "The database connection is closed"
        c = self._db.cursor()
        return cursor(c, self)

    # NONAPI
    def prepare(self, statement=None):
        if self._db is None:
            raise InterfaceError, "The database connection is closed"

        if statement is None:   # Meant to do 2-phase commit prepare 
           return self._db.prepare()

        # Backwards compatible DCOracle prepare -- prepare a cursor with
        # a pre-prepared statement. (Not pre-bound though)
        c = self.cursor()
        c.prepare(statement)
        return c

    # NONAPI
    def objects(self, all=0):
        """Return a list of objects, either ALL objects (all=1) or
        user objects, by selecting from ALL_OBJECTS or USER_OBJECTS"""

        if self._db is None:
            raise InterfaceError, "The database connection is closed"

        # DCO 1 can enumerate objects, so we'll provide that feature
        if all:
            t = 'ALL'
            e = ', OWNER'
        else:
            t = 'USER'
            e = ''
        c = self._cursor   # Use the implicit cursor
        c.execute(
            'select OBJECT_NAME, OBJECT_TYPE%s from %s_OBJECTS where STATUS != \'INVALID\'' % (e,t)
            )
        # Grab the results
        r = c.fetchall()
        return r

    # NONAPI
    def getSource(self, proc):
        """Get the source of a stored procedure (proc)"""

        if self._db is None:
            raise InterfaceError, "The database connection is closed"

        c = self.cursor()
        proc = upper(proc)
        c.execute(
            'select LINE, TEXT from ALL_SOURCE where NAME=:1', proc
            )
        r = []
        for line, text in c.fetchall(): r.append((line, text))
        #r.sort()
        return join(map(lambda i: i[1], r),'')

    # NONAPI
    def traceback(self,format=0):
        """Return the database traceback"""
        return DCOracle2.traceback(format)

    # NONAPI
    def describe(self, name):
        """Call the Oracle describe function on a schema object."""

        if self._db is None:
            raise InterfaceError, "The database connection is closed"

        return self._db.describe(name)

    # NONAPI
    def BindingArray(self,*args):
        """BindingArray([count, [itemsize, [type]]]) --
        Allocates a new binding array -- while the arguments are optional,
        a dynamic OUT bind will probably fail in unusual ways if the
        parameters given to the BindingArray are incorrect.  A BindingArray
        will be grown if necessary, so count does not have to encompass ALL
        results.
        
        Type may be numeric or string literal e.g. 4 or 'SQLT_STR' both
        instruct Oracle to format the result as a null terminated string.
        
        A type error will result on access of a BindingArray element if
        no output type converter is available for the data type."""

        return apply(self._db.BindingArray,args)

    # NONAPI
    def LobLocator(self,*args):
        """Allocate an empty lob locator -- used for SP invocation"""
        return apply(self._db.LobLocator,args)

    # NONAPI
    def rowid(self, *args):
        """Allocate an empty rowid -- use for SP invocation"""
        return apply(self._db.rowid, args)

    # API pass-through for internal cursor
    def __getattr__(self, name):
        d = self.__dict__

        if d.has_key('_cursor'): c = d['_cursor'] # If internal cursor exists...
        else: d['_cursor'] = c = self.cursor()    # otherwise, create it
        if name == '_cursor': return c
        if name == 'version': return self.Version()

        if hasattr(c, name): return getattr(c, name) # pass through
        raise AttributeError, name

    # API pass through for internal cursor
    def __setattr__(self, name, value):
        d = self.__dict__
        if name == 'arraysize': self._cursor.arraysize = value
        else:
            raise AttributeError, 'Unassignable attribute, '+name


    # NONAPI
    def Version(self):
        d = {}
        c = self.cursor()
        try:
            c.execute("SELECT * FROM PRODUCT_COMPONENT_VERSION")
        except DatabaseError:
            return None
        for (product, vers, production) in c.fetchall():
            d[product] = "%s (%s)" % (vers, production)
        del c
        d['DCOracle2'] = version 
        self.__dict__['version'] = d
        return d

    # NONAPI
    #
    # collapsedesc performs a lot of labor shrinking a description returned
    # by the schema describe() function into a more parseable form
    def collapsedesc(self, desc):
        """Collapses a description, returning a LIST of
        [schema, name, type, contents]"""
        ptypetab = {
            1: "table",       
            2: "view",
            3: "procedure",
            4: "function",
            5: "package",
            6: "type",
            7: "synonym",
            8: "sequence",
            9: "column",
            10: "argument",
            11: "list",
            17: "schema",
            18: "database"
        }

        if desc.has_key('OCI_ATTR_OBJ_SCHEMA'):
            schema = desc['OCI_ATTR_OBJ_SCHEMA']
        else:
            schema = ""

        if desc.has_key('OCI_ATTR_OBJ_NAME'):
            name = desc['OCI_ATTR_OBJ_NAME']
        elif (desc.has_key('OCI_ATTR_NAME')):
            name = desc['OCI_ATTR_NAME']
        else:
            name = ""

        ptype = desc['OCI_ATTR_PTYPE']
        if ptypetab.has_key(ptype):
            type = ptypetab[ptype]
        else:
            type = str(ptype)

        if desc.has_key('OCI_ATTR_DATA_TYPE'):
            dtype = desc['OCI_ATTR_DATA_TYPE']
        else: dtype = ()

        if desc.has_key('OCI_ATTR_LIST_ARGUMENTS'):
            args = desc['OCI_ATTR_LIST_ARGUMENTS']
        else: args = None

        answer = [schema, name, type]

        if type == "package":
            subprogs = desc['OCI_ATTR_LIST_SUBPROGRAMS']
            contains = []
            for i in range(0, len(subprogs)):
                contains.append(self.collapsedesc(subprogs[i]))
            answer.append(contains)

        if type == "function" or type == "procedure":
            contains = []
            for i in range(0, len(args)):
                contains.append(self.collapsedesc(args[i]))
            answer.append(contains)

        if type == "argument" or type == "column":
            if type == "argument":
                hasdef = int(desc['OCI_ATTR_HAS_DEFAULT'])
                mode = desc['OCI_ATTR_IOMODE']
                if mode == 0:
                    if hasdef:
                        inout = ("IN","DEFAULT") 
                    else:
                        inout = ("IN",) 
                elif mode == 1: inout = ("OUT",)
                else:
                    if hasdef:
                        inout = ("IN","OUT","DEFAULT")
                    else:
                        inout = ("IN","OUT")
            else: inout = ()

            size = desc.get('OCI_ATTR_DATA_SIZE',0)
            precision = desc['OCI_ATTR_PRECISION']
            scale = desc['OCI_ATTR_SCALE']
            isnull = desc['OCI_ATTR_IS_NULL']

            answer.append((dtype, size, precision, scale, isnull, inout))

            if dtype == 251: # Table of
                answer[-1] = answer[-1] + ( self.collapsedesc(args[0]) ,)

        if type == "table" or type == "view":
            columns = desc['OCI_ATTR_LIST_COLUMNS']
            contains = []
            for i in range(0, len(columns)):
                contains.append(self.collapsedesc(columns[i]))
            answer.append(contains)

        if type == "synonym":
            answer.append("%s.%s" % (
                desc['OCI_ATTR_SCHEMA_NAME'],
                desc['OCI_ATTR_NAME']
                ))

        if len(answer) < 4: answer.append(None)

        return answer
                

    # NONAPI
    def decodedesc(self, desc, indent=1):
        """Decode a schema description result into human-readable output"""
        ptypetab = {
            1: "table",       
            2: "view",
            3: "procedure",
            4: "function",
            5: "package",
            6: "type",
            7: "synonym",
            8: "sequence",
            9: "column",
            10: "argument",
            11: "list",
            17: "schema",
            18: "database"
        }

        if desc.has_key('OCI_ATTR_OBJ_SCHEMA'):
            if desc['OCI_ATTR_OBJ_SCHEMA'] is not None:
                schema = desc['OCI_ATTR_OBJ_SCHEMA'] + '.'
            else:
                schema = ""
        else:
            schema = ""

        if desc.has_key('OCI_ATTR_OBJ_NAME'):
            name = desc['OCI_ATTR_OBJ_NAME']
        elif (desc.has_key('OCI_ATTR_NAME')):
            name = desc['OCI_ATTR_NAME']
        else:
            name = ""

        ptype = desc['OCI_ATTR_PTYPE']
        if ptypetab.has_key(ptype):
            type = ptypetab[ptype]
        else:
            type = str(ptype)

        if desc.has_key('OCI_ATTR_DATA_TYPE'):
            dtype = Type(desc['OCI_ATTR_DATA_TYPE'])
        else: dtype = ""

        if desc.has_key('OCI_ATTR_LIST_ARGUMENTS'):
            args = desc['OCI_ATTR_LIST_ARGUMENTS']
        else: args = None

        if type == "function":
            precision = args[0]['OCI_ATTR_PRECISION']
            scale = args[0]['OCI_ATTR_SCALE']
            rtype = Type(args[0]['OCI_ATTR_DATA_TYPE'])
            if rtype == "NUMBER" and scale != 0:
                rtype = "%s(%s,%s)" % (rtype, precision, scale)
            elif rtype == "NUMBER" and precision == 38 or precision == 0:
                pass
            elif precision != 0 :
                rtype = "%s(%s)" % (rtype, precision)
            returns = "returns %s " % rtype
        else: returns = ""

        answer = "%s%s %s %s" % (schema, name, type, returns)

        if type == "package":
            subprogs = desc['OCI_ATTR_LIST_SUBPROGRAMS']
            for i in range(0, len(subprogs)):
                answer = "%s\n%s%s" % (answer, "\t" * indent,
                    self.decodedesc(subprogs[i],indent+1))

        if type == "function" or type == "procedure":
            if type == "function": start = 1
            else: start = 0
            for i in range(start, len(args)):
                answer = "%s\n%s%s" % (answer, "\t" * indent,
                    self.decodedesc(args[i],indent+1))

        if type == "synonym":
            answer = "%sfor %s.%s" % (answer, 
                desc['OCI_ATTR_SCHEMA_NAME'],
                desc['OCI_ATTR_NAME']
                )

        if type == "argument" or type == "column":
            if type == "argument":
                mode = desc['OCI_ATTR_IOMODE']
                hasdef = int(desc['OCI_ATTR_HAS_DEFAULT'])
                if mode == 0: inout = "IN" 
                elif mode == 1: inout = "OUT"
                else: inout = "IN OUT"
                if hasdef: inout = inout + " DEFAULT"

            precision = desc['OCI_ATTR_PRECISION']
            scale = desc['OCI_ATTR_SCALE']
            size = desc['OCI_ATTR_DATA_SIZE']
            if dtype == "NUMBER":
                if scale != 0:
                    dtype = "%s(%s,%s)" % (dtype, precision, scale)
                elif precision != 38 and precision != 0:
                    dtype = "%s(%s)" % (dtype, precision)
            elif dtype == "TABLE": dtype = 'TABLE OF'
            elif dtype == "REF CURSOR": pass
            elif precision != 0:
                dtype = "%s(%s)" % (dtype, precision)
            elif (dtype == "VARCHAR2" or dtype == "CHAR") and (
                type != "argument"):
                dtype = "%s(%s)" % (dtype, size)
            if desc['OCI_ATTR_IS_NULL'] == 0 and type != "argument":
                dtype = dtype + " NOT NULL"

            if type == "argument": answer = "%s %s %s" % (answer, inout, dtype)
            else: answer = "%s %s" % (answer, dtype)

        if type == "table" or type == "view":
            columns = desc['OCI_ATTR_LIST_COLUMNS']
            for i in range(0, len(columns)):
                answer = "%s\n%s%s" % (answer, "\t" * indent,
                    self.decodedesc(columns[i],indent+1))
        elif dtype == 'TABLE OF':
            columns = desc['OCI_ATTR_LIST_ARGUMENTS']
            for i in range(0, len(columns)):
                answer = "%s %s" % (answer, 
                    join(split(self.decodedesc(columns[i],indent+1))[3:]))

        return answer

    def decodecdesc(self, desc, oname=None, indent=0):
        """Decode a collapsed schema description into human-readable output"""
        answer = "\t" * indent
        if type(desc) == types.ListType: desc = { oname: desc }
        for (fname, entry) in desc.items():
            (schema, name, etype, sdesc) = entry
            if oname is not None: name = oname

            if etype == "package": 
                answer = "%spackage %s contains:\n" % (
                    answer, name)
                for d in sdesc:
                    answer = "%s%s" % (answer,
                        self.decodecdesc(d, indent=indent+1))
            elif etype == "procedure":
                answer = "%sprocedure %s has arguments:\n" % (
                    answer, name)
                for d in sdesc:
                    answer = "%s%s" % (answer, self.decodecdesc(d,
                        indent=indent+1))
            elif etype == "function":
                answer = "%sfunction %s returns %s, has arguments:\n" % (
                    answer, name, 
                    self.decodecdesc(sdesc[0:1][0])[0:-1])
                for d in sdesc[1:]:
                    answer = "%s%s" % (answer, self.decodecdesc(d,
                        indent=indent+1))
            elif etype == "synonym":
                answer = "%ssynonym for %s" % (answer, sdesc)
            elif etype == "argument":
                if name is not None:
                    answer = "%s%s " % (answer, name)
                if len(sdesc) > 6:
                   (dtype, size, precision, scale, isnull, inout, tabof) = sdesc
                else:
                   (dtype, size, precision, scale, isnull, inout) = sdesc
                   tabof = ()
                dtype = Type(dtype)
                if dtype == "NUMBER":
                    if scale != 0:
                        dtype = "%s(%s,%s)" % (dtype, precision, scale)
                    elif precision != 38 and precision != 0:
                        dtype = "%s(%s)" % (dtype, precision)
                elif dtype == "REF CURSOR": pass
                elif dtype == "TABLE":
                    dtype = "TABLE OF %s" % self.decodecdesc(tabof)[2:]
                elif precision != 0:
                    dtype = "%s(%s)" % (dtype, precision)
                answer = "%s%s %s\n" % (answer, join(inout), dtype)
        return answer
                
    # NONAPI
    def mapproc(self, name):
        """Maps the named procedure or package so that it is callable."""
        name = upper(name)

        #print "mapping proc %s on %s " % (name, self)

        if hasattr(self,"_procdesc") and self._procdesc.has_key(name):
            d = self._procdesc[name]
            #print "using saved def %s" % d
        else:
            d = self.collapsedesc(self.describe(name))
            self.__dict__['_procdesc'][name] = d
            #print "saving desc %s" % name

        proc = {}

        (sname, uname, type, desc) = d

        if type == "procedure" or type == "function" or type == 'synonym':
            proc[name] = d
        elif type == "package":
            proc[name] = d
            for d1 in desc:
                (sname, uname, type, d2) = d1
                proc['%s.%s' % (name, uname)] = d1

        return proc

    # NONAPI
    def getTransactionName(self):
        """Returns the transaction name associated with the current
           transaction, or None via the OCI_ATTR_TRANS_NAME attribute"""

        trans = self._db.getTransaction()
        if not trans: return None

        return trans.name

    # NONAPI
    def setTransactionName(self, name):
        """Sets the transaction name associated with the current
           transaction, or None via the OCI_ATTR_TRANS_NAME attribute"""

        trans = self._db.newTransaction()

        name = str(name)        # XXX will wedge unicode names

        if len(name) > 64:
            raise ValueError, ('transaction name too long', name)

        trans.setName(name)
        self._db.setTransaction(trans)

    # NONAPI
    def getTransactionXID(self):
        """Returns the transaction XID as a (format, globalid, branch qualifier)
           tuple, or None if there is no transaction XID"""

        trans = self._db.getTransaction()
        if not trans: return None

        return (trans.format, trans.gtrid, trans.bqual)

    # NONAPI
    def setTransactionXID(self, xid):
        """Sets the transaction XID associated with the current
           transaction.  XID is passed in as a tuple
           (format, globalid, branch qualifier)"""

        trans = self._db.newTransaction()

        trans.setXID(xid[0], xid[1], xid[2])

        self._db.setTransaction(trans)

#
# cursor objects
#
# Cursors track the underlying dco2 cursor object as well as retaining
# knowledge of their parent connection
#
# Internal attributes:
#
#   _cursor         dco2's cursor object
#   _result         saved result set from dco2.fetch() used to un-batch
#                   dco2's result set batching
#   _rcount         A row count for result unbatching, different from
#                   the rowcount rows processed 
#   _operation      Last statement issued, used to skip a prepare if
#                   the last statement is reused
#   _sizes          An array of output sizes for stored procedure use,
#                   modified by setoutputsize()
#   _mapproc        Procedures mapped by callproc()
#
#   _prefetch       a tuple of (size, records) to set the statement
#                   handle prefetch to after a prepare() operation
#

_nonullmap = []

class cursor:
    
    _cursor = None
    _result = None
    _rcount = -1
    _operation = None
    _connection = None
    _mapproc = {}
    _prefetch = (20,1048576) # default 20 records, size <= 1M
    arraysize = 20
    rowcount = -1
    description = None
    __allow_access_to_unprotected_subobjects__ = 1  # For Zope

    def __init__(self, c, connection):
        self._cursor = c
        self._connection = connection
        self._sizes = []
        self._mapproc = {}
        self._nullmap = None

    # support iteration over a cursor
    def __iter__(self):
        return self

    def next(self):
        x = self.fetchone()
        if x == None:
            raise StopIteration
        return x

    # NONAPI
    def describe(self,raw=None):
        d = []
        desc = self._cursor.describe()
        if desc is None: return None
        for name, typ, size, storage, precision, scale,  nullok in desc:
            if raw:
                t = typ
            else:
                t = Type(typ)
                if t is None:
                    t = typ
            d.append((name, t, size, storage, precision, scale, nullok))

        return d

    # NONAPI
    def findproc(self, procname):
        procname = upper(procname)
        proc = self._mapproc.get(procname,None)
        if proc is None:
            # ok, we don't have it mapped, so go get it, piecewise, from
            # the connection procedures object
            proc = self._connection.procedures
            for e in split(procname,'.'):
                proc = getattr(proc,e)
        if proc is None: # shouldnt ever get here (the proc getattr raises)
            raise ProgrammingError,"Procedure %s not found" % procname

        self._mapproc[procname] = proc
        
        return proc

    def callproc(self, procname, *params, **kw):
        proc = self.findproc(procname)

        kw['__allreturn'] = 1
        return apply(proc, params, kw)
                

    def close(self):
        self.procedure = None
        self._result = None
        self._cursor.close()
        self._cursor = None
        self._connection = None
        self._nullmap = None

    # NONAPI
    def isOpen(self):
        "Is our internal connection around?"
        return self._cursor is not None

    def prepare(self, statement):
        self._cursor.prepare(statement)
        self._operation = statement
        self._nullmap = {}

    # execute
    #
    # the special keyword parameter is '__plist' which the stored
    # procedure __call__ method uses to invoke with a list of paramters

    #
    # The _nonullmap and _nullmap business is to force a re-prepare when
    # an initial bind was done with a NULL parameter (None) which gets bound
    # as SQLT_STR.  The nullmap maps which parameters are None -- after an
    # initial execute, if no entries are in the map, it is replaced with
    # _nonullmap, an empty list; which is a signal to stop checking it until
    # the next normal prepare.
    #
    def execute(self, operation=None, *params, **kw):
        if self._cursor is None:
            raise InterfaceError,"cursor is closed"

        if operation is None: operation = self._operation

        if self._operation != operation or self._nullmap is None:
            self._cursor.prepare(operation)
            self._operation = operation
            self._nullmap = {}

            if self._prefetch:
                apply(self._cursor.setPrefetch, self._prefetch)

        if operation is None:
            raise ProgrammingError, "execute requires a SQL statement"

        i = 0
        if kw.has_key('__plist'):
            params = kw['__plist'] 
            del kw['__plist']           # bye bye -- cleaned up kw!

        if len(params) == 1 and len(kw) == 0: # Is this a format of (args,...)?
            t = type(params[0])
            if t is types.TupleType: params = params[0] # oldstyle (strict) parm

        if len(params) > 0:  # Is this the old many form?
            t = type(params[0])
            if t is types.TupleType or t is types.ListType:
                # sigh convert from column major to row major for executemany
                p = []
                for i in xrange(len(params[0])):
                    r = []
                    for pgroup in xrange(len(params)):
                        r.append(params[pgroup][i])
                    p.append(r)
                return self.executemany(operation,p)

        #print operation

        # Bind positional parameters
        for p in params:
            i = i + 1
            if p is None and self._nullmap is not _nonullmap:
                self._nullmap[i] = 1
            #print "binding %d as %s" % (i, p)
            if isTypeCoercion(p):
                self._cursor.bindbypos(i, p.value, p.type)
            else:
                self._cursor.bindbypos(i, p)

        # Bind named parameters
        for key in kw.keys():
            ck = ":" + key
            #print "binding %s as %s" % (ck, kw[key])
            p = kw[key]
            if self._nullmap is not _nonullmap: # dco2 flushes types after named
                self._nullmap[key] = 1          # binds so always reprepare
            if isTypeCoercion(p):
                self._cursor.bindbyname(ck, p.value, p.type)
            else:
                self._cursor.bindbyname(ck, p)

        result = self._cursor.execute()
        if result in RowCountResults:
            self.rowcount = self._cursor.rowcount()
        else:
            self.rowcount = -1
        self.description = self.describe()
        if result == 8:
            so = "PROC: %s" % self._operation
            if self._operation != so: self._operation = so # Force rebind
        if not result == 1:
            self.description = None

        if len(self._nullmap) > 0:
            self._nullmap = None
        else:
            self._nullmap = _nonullmap

        return result

    def executemany(self, operation, params):
        if self._cursor is None:
            raise InterfaceError,"cursor is closed"
        prepared = 0
        if self._operation != operation:
                self._cursor.prepare(operation)
                self._operation = operation
                prepared = 1
                self._nullmap = None

        # Potential note for named parameters:  coerce all positional 
        # parameters to named parameters 'by number' e.g. using the postion
        # as the key in the name.  Then the remainder of the code which uses
        # params can use the keys of the named parameters instead.

        # Convert parameters from row major to column major
        # We take parameters in like ((a1, b1, c1), (a2, b2, c2)) so
        # convert to ((a1, a2), (b1, b2), (c1, c2))

        rows = len(params)
        columns = len(params[0])
        size = []
        first = 1

        BATCHSZ = self.arraysize

        if BATCHSZ > rows: BATCHSZ = rows
        notnull = []

        for c in xrange(columns): notnull.append(0)

        # First, figure out the maximum column widths
        for r in xrange(rows):
            for c in xrange(columns):
                p = params[r][c]
                if p is not None: notnull[c] = 1
                try:
                    if isTypeCoercion(p):
                        l = len(p.value) + 1
                    else:
                        l = len(p) + 1
                except:
                    l = 4   # Word size kludge
                if r == 0: size.append(l)
                elif size[c] < l: size[c] = l

        # if any columns were all None, we force a prepare to flush out
        # old bindings -- note that a duplicate prepare can result when
        # execute() does a prepare, then calls executemany() with a column
        # of all NULLs

        if prepared == 0:
            for c in xrange(columns):
                if notnull[c] == 0:
                    self._cursor.prepare(operation)
                    self._nullmap = None
                    self._operation = self._operation + " " # force rebind next
                    break

        # Second, create a binding array for each column
        baoa = []
        for c in xrange(columns):
            # Don't try to use None as a type -- keep going for non None
            # to get real type
            for i in xrange(rows):
                p = params[i][c]
                if p is not None: break
            if isTypeCoercion(p):

                p = p.value
                t = p.type
                mytype = t
            else:
                t = type(p)
                if t == dco2.OracleDateType:
                    size[c] = 7
                    mytype = 'SQLT_DAT'
                elif t == dco2.OracleRowIDType:
                    size[c] = 4 # FIXME dont know what size it is
                    mytype = 'SQLT_RDD'
                elif t == types.FloatType:
                    size[c] = 8
                    mytype = 'SQLT_FLT'
                elif t == types.IntType:
                    size[c] = 4 # XXX WRONG on 64 bit machines
                    mytype = 'SQLT_INT'
                elif t == types.LongType:
                    size[c] = 45 # Punt on size to 22 base 100 digits
                    mytype = 'SQLT_STR'
                    p = str(p)  # Convert the long to a string
                else:
                    mytype = 'SQLT_STR'

            # Grab & append a new binding array
            baoa.append(self._connection.BindingArray(BATCHSZ, size[c],
                        mytype))

            # XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX
            # XXX                                                 XXX
            # XXX  This next line controls if this is a static    XXX
            # XXX  bind or not.  Static binds are necessary on    XXX
            # XXX  some systems and will break on others.         XXX
            # XXX                                                 XXX
            # XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX

            #baoa[c].setStatic(1)    # Mark this as a static binding

            # ERROR -- this is too early for a normal binding array bind
            #self._cursor.bindbypos(c + 1, baoa[c])

        # now populate the binding array by our batch size
        for i in xrange((rows + BATCHSZ -1) / BATCHSZ):
            batchstart = i * BATCHSZ
            batchend = batchstart + BATCHSZ;
            if batchend > rows: batchend = rows
            for r in xrange(batchstart, batchend):
                br = r - batchstart
                for c in xrange(columns):
                    p = params[r][c]
                    #print p
                    if isTypeCoercion(p):

                        baoa[c][br] = p.value
                    else:
                        baoa[c][br] = p
            for c in xrange(columns):
                # (re-)bind the binding array so it has the proper length
                self._cursor.bindbypos(c + 1, baoa[c])

            # Execute the batch
            result = self._cursor.execute(batchend-batchstart)
            self.rowcount = self._cursor.rowcount()

        self.description = self.describe()

        del baoa

        return result

    def fetchone(self,skip=0):
        if self._cursor is None:
            raise InterfaceError,"cursor is closed"
        if self._result is not None and self._rcount >= len(self._result[0]):
            self._result = None
            self._rcount = -1

        if self._result is None:
            self._result = self._cursor.fetch(self.arraysize)
            self._rcount = 0

        l = []

        if skip > 0:
            while skip:
                advance = skip
                setremain = len(self._result[0]) - self._rcount
                if advance > setremain: advance = setremain
                self._rcount = self._rcount + advance
                skip = skip - advance
                if self._rcount >= len(self._result[0]):
                    self._result = self._cursor.fetch(self.arraysize)
                    self._rcount = 0

        for col in self._result:  # Result set is column based
            try:
                v = col[self._rcount].value()
            except IndexError:
                self._result = None
                return None
            # Note that if we got back a dco2.cursor, we need to wrap it
            t = type(v)

            if t == dco2.CursorType:
                v = cursor(v,self._connection)
                v.description = v.describe()
            elif t == dco2.OracleDateType and DateConversion:
                v = DateConversion(v)

            l.append(v)

        self._rcount = self._rcount + 1
        self.rowcount = self._cursor.rowcount()

        #print "fetchone() returning %s" % l

        return l
        
    def fetchmany(self, size=None, skip=0):
        if self._cursor is None:
            raise InterfaceError,"cursor is closed"
        a = []

        if size is None: size = self.arraysize

        # Wisdom: using range(0,size) is bad when size comes in from the
        # default query as 999999
        i = 0
        while i < size:
            v = self.fetchone(skip=skip)
            if v is None: break
            a.append(v)
            i= i+1
            skip = 0

        self.rowcount = self._cursor.rowcount()
        return a

    def fetchall(self, skip=0):
        if self._cursor is None:
            raise InterfaceError,"cursor is closed"
        a = []

        while 1:
            v = self.fetchone(skip=skip)
            if v is None: break
            a.append(v)
            skip = 0

        self.rowcount = self._cursor.rowcount()
        return a

    def setPrefetch(self, records, size=0):
        """setPrefetch(records, [size=0]) -- sets the prefetch to records,
           constrained in size bytes"""
        if records <= 0:
            self._prefetch = None
        else:
            self._prefetch = (records, size)

    def setinputsizes(self, sizes):
        if self._cursor is None:
            raise InterfaceError,"cursor is closed"
        pass

    def setoutputsize(self, size, *params):
        if self._cursor is None:
            raise InterfaceError,"cursor is closed"
        # Lets make sure the range is large enough
        for i in xrange(len(self._sizes), size+len(params)):
            self._sizes.append(None)
        # OK set the size(s) into the size array
        for i in xrange(size, size+len(params)):
            self._sizes[i] = params[i - size]

    def nextset(self):
        raise NotSupportedError, "cursor.nextset() is not supported"

    def __getattr__(self, name):
        d = self.__dict__
        if name == 'procedure' or name == 'procedures':
            name = 'procedures'
            if d.has_key(name): return d[name]
            d[name] = p = procedure(self)
            return p            # Return the procedure object

        if d.has_key(name): return d[name] 
        raise AttributeError, name


#
# Wrap stored procedures
#
#
# It's worth re-iterating how the collapsed descriptions are stored:
# They are in (schema, name, type, params) format.  The params is frequently
# a list of more collapsed descriptions, or can be in the form
#   [type, size, precision, scale, isnull, inout]
# so, something like self._description[3][0][3][5] means for the 0'th (first)
# item in the list of parameters (the first [3]), we again look at the parameter
# list and look at the 'inout' field.  
# 
class procedure:

    _cursor = None
    _description = None
    __name__ = None
    _exec = 0
    _schema = None
    _size = []
    def __init__(self, cursor, schema=None):
        self._cursor = cursor
        self._schema = schema
        self._argList = None


    def argList(self):
        """ Return the arguments to a stored procedure, in a list of tuples
            of (name, type, mode).  Mode is itself a tuple, containing
            possible values IN, OUT, and DEFAULT.
        """
        if self._exec == 0: return []

        if self._argList is not None: return self._argList

        result = []

        procdesc = self._description[3]

        for arg in procdesc:
            argDesc = arg[3]
            result.append((arg[1], argDesc[0], argDesc[5]))

        self._arglist = result

        return result

    # Procedure calls take two "hidden" parameters, used by cursor.callproc
    # __allreturn is used to specify that ALL parameters are to be returned,
    #   not just OUT parameters 

    def __call__(self, *args, **kw):
        #print "call: args %s, kws: %s" % (args, kw)
        #print "_name = %s, _schema=%s, _description=%s" % (self._name, self._schema, self._description)
        if self._exec == 0:
            raise ProgrammingError, "%s not found, presumed schema" % self.__name__

        argsused = 0
        argmap = []
        argconvert = []
        arglist = []

        procdesc = self._description[3]
        
        if self._description[2] == "function":
            fcn = ":1 := "          # fcn is base statement being built up
            base = 1
            # Add to the argument map the [Name, type, description])
            argmap.append([None, procdesc[0][3][5],
                          procdesc[0][3]])
            argconvert.append(None)
        else:
            fcn = ""
            base = 0

        n = self.__name__

        cursor = self._cursor

        if kw.has_key("__allreturn"):
            allreturn = kw["__allreturn"]
            del kw["__allreturn"]
        else:
            allreturn = None


        keymap = {}

        # Keymap is an upper-cased version of our keyword dictionary
        for k in kw.keys():
            ku = upper(k)
            keymap[ku] = kw[k]


        # Process all of the arguments for the procedure/function, either
        # drawing them from the keyword arguments (by name) or from the
        # argument list (by position)  

        # The argmap is a list of the actual arguments, in the form
        # [value, mode, description]

        # The arglist is a list of the NAMES of the arguments

        #print "procedure %s" % self.__name__

        defaults = 0

        # Iterate through each argument in the proc definition
        for i in range(base,len(procdesc)):
            d = procdesc[i]     # d is the description string for arg i
            thisargName = d[1]  # v is the parameter name for arg i 
            # Now, if this is an IN parameter?
            #print d
            thisargDesc = d[3]  # The description for this argument
            mode = thisargDesc[5] # mode is the mode (IN/OUT/DEFAULT)

            if 'IN' in mode:
                if keymap.has_key(thisargName):
                    argmap.append([keymap[thisargName], mode, thisargDesc])
                    #print "Binding argument %s= %s" % (thisargName, keymap[thisargName])
                else:
                    if len(args) <= argsused:
                        if 'DEFAULT' in mode:
                            defaults = defaults + 1
                            continue
                        raise ValueError, "Not enough arguments"
                    if 'DEFAULT' in mode and args[argsused] is None:
                            defaults = defaults + 1
                            continue
                    argmap.append([args[argsused], mode, thisargDesc])
                    #print "Binding argument %s= %s" % (thisargName, args[argsused])
                    argsused = argsused + 1
            # Otherwise, it's an OUT parameter, and we pass in NULL for those
            else: 
                argmap.append([None, mode, thisargDesc])

            argconvert.append(None)

            thisargindex = str(i + 1 - defaults)

            if defaults:
                arglist.append("%s => :%s" % (thisargName, thisargindex))
            else:
                arglist.append(":%s" % thisargindex) 

        if len(arglist) > 0:
            alist = ','.join(arglist)
        else:
            alist = ""

        # Note:  If we were clever, we might put in extra lines in the
        # statement, so that "error on line 1" would be more specific about
        # which parameter was having problems.
        sql = "BEGIN %s %s(%s); END;" %  (fcn, n, alist)

        #print sql
        #print argmap

        for i in xrange(0, len(argmap)):
            a = argmap[i]   # The argument map for this argument

            thisargValue = a[0]
            thisargMode = a[1]
            thisargDesc = a[2]

            # doesn't properly handle IN OUT
            if ('OUT' in thisargMode and type(thisargValue) is
                not dco2.BindingArrayObjectType):

                # BindingArrays are made with count, size, type
                l = thisargDesc[2]      # Length
                if cursor is not None:
                    try:
                        l = cursor._sizes[i]
                    except IndexError: pass
                    if l is None or l == 0: l = DEFAULTPROCSIZE
                # Need to use the setoutputsize parameter
                if l == 0: 
                    l = DEFAULTPROCSIZE

                dty = thisargDesc[0]    # data type
                count = 1

                # Based on the data type, we may need to do some special
                # transforms (e.g. sending numbers through as strings)

                if dty == 251: # PL/SQL Table of 
                    # go find out what the table is really of
                    tabof = thisargDesc[6][3] # secondary description
                    dty = tabof[0]
                    l = tabof[1]
                    if len(cursor._sizes) > i:
                        count = cursor._sizes[i] or DEFAULTPROCCOUNT
                    else:
                        count = DEFAULTPROCCOUNT
                elif ((dty == 112 or dty == 113) and
                    type(thisargValue) is not dco2.LobLocatorType):
                    # BLOB/CLOB coming out, so make a locator element
                    # (doesnt handle an array of BLOBS)
                    thisargValue = cursor._connection.LobLocator(dty)
                    a[0] = thisargValue
                    break   # Not a binding array, but a lob locator
                elif dty == 11: # ROWID (old fmt)
                    dty = 'SQLT_RDD'
                    l = 4
                elif dty == 102:  # SQLT_CUR
                    dty = 'SQLT_RSET'
                    l = 4   # Was 38 ?  harumph
                elif dty == 2 and 'IN' not in thisargMode: 
                    # SQLT_NUM but not IN OUT
                    if NativeNumeric:
                        dty = 'SQLT_VNU'
                        l = 23
                    else:
                        dty = 'SQLT_STR'
                        argconvert[i] = 2

                #print "Creating new BindingArray of size %s, type %s" % (l, dty)
                ba = cursor._connection.BindingArray(count, l, dty)

                #if count == 1:
                #    ba.setStatic(1) # XXX for testing only (OUT BA's must grow!)
                olda = thisargValue
                a[0] = ba  # Swap in our new binding array for the output

                # I'm not going to worry about arrays of cursors going in
                if dty == 'SQLT_RSET':  
                    if not 'IN' in thisargMode:
                        ba[0] = TypeCoercion(
                            cursor._connection.cursor(), dty)
                    else:
                        if type(olda) is not types.InstanceType:
                            # It probably wasnt a cursor going in -- make one
                            olda = cursor._connection.cursor()
                        ba[0] = TypeCoercion(olda, dty)
                elif dty == 'SQLT_RDD':
                    # dco2 will puke if you pass a non-ROWID in as a RDD
                    if not 'IN' in thisargMode:
                        # Take a rowid vs a binding array
                        a[0] = cursor._connection.rowid()
                elif 'IN' in thisargMode:    # This is an in/out parameter
                    ba[0] = olda      # FIXME doesnt do the array case
                    ba = self._argconvertIn(ba, argconvert, i)
                    a[0] = ba         # Resave new arg


        #print argmap

        args = []
        for a in argmap:
            args.append(a[0])
        #print "Executing %s with %s" % (sql, args)
        # FIXME convert this to an apply
        cursor.execute(sql,__plist=args)
        results = []

        # We construct a result set that is either a list or a singleton
        # so that functions and single OUT procedures behave like functions,
        # but multi-OUT procedures return a list of all OUT parameters.

        for ai in xrange(0, len(argmap)):
            a = argmap[ai]
            if argconvert[ai] is not None:
                a[0] = self._argconvertOut(a[0], argconvert, ai)
            #print a[0]
            if 'OUT' in a[1] or allreturn:

                if type(a[0]) is dco2.BindingArrayObjectType:
                    #print "bindingarray: len %d" % len(a[0])
                    if len(a[0]) == 1:
                        #print "bindingarray contents:"
                        #print a[0][0]
                        v = a[0][0]

                        # If we got a single cursor, describe it
                        if isinstance(v, cursor.__class__):
                            v.description = v.describe()
                        results.append(v)
                    else:
                        #print "bindingarray contents:"
                        t = []
                        for i in xrange(len(a[0])):
                            #print a[0][i]
                            v = a[0][i]
                            # If we got a cursor, describe it
                            if isinstance(v, cursor.__class__):
                                v.description = v.describe()
                            t.append(v)
                        results.append(t)   # converted into a list
                else:
                    results.append(a[0])    # doesnt have len
        if len(results) == 1: results=results[0]
        #print "returning: %s" % results
        return results

    def __getattr__(self, name):
        #print "procedure getattr: %s" % name
        if name[0:2] == "__":           # handle loopback to __str__ etc
            raise ValueError, name

        d = self.__dict__

        if d.has_key(name): return d[name]
        name = upper(name)
        if d.has_key(name): return d[name]

        if self._schema is not None:
            sname = "%s.%s" % (self._schema, name)
        else:
            sname = name       

        cursor = self._cursor
        inpkg = 0

        try:
            desc = cursor._connection.mapproc(sname)
        except DatabaseError:
            # With Oracle 8i, error 4043 is object does not exist, so
            # it MIGHT be a schema name... so let's make a dummy object
            # and return
            if self._schema is not None:
                # Whoops, we've already been this route
                if find(self._schema,'.') > -1: stype = "package"
                else: stype = "schema"
                raise ProgrammingError, "%s %s does not contain %s" % (
                    stype, self._schema, name)

            newproc = procedure(cursor,schema=name)
            newproc.__name__ = name
            #print "Creating schema %s" % name
            d[name] = newproc
            return newproc

        #print desc

        #print "Searching for %s" % sname

        if (desc.has_key(sname) and desc[sname][2] == 'package'):
            (schema, uname, type, pdesc) = desc[sname]
            if uname is None: uname = name
            # Set up the package
            if self._schema is None: sname = name
            else: sname = "%s.%s" % (self._schema, name)
            newproc = procedure(cursor,schema=sname)
            if self._schema is not None:
                newproc.__name__ = "%s.%s" % (self._schema, name)
            else:
                newproc.__name__ = name
            newproc._description = desc[newproc.__name__]
            #print "Creating package object %s" % (uname)
            d[uname] = newproc
            newproc.__doc__ = cursor._connection.decodecdesc(
                newproc._description, newproc.__name__)
            d = newproc.__dict__    # Temporarily override the target dict
            inpkg = 1

        # Do all entries within the package
        for p in desc.keys():
            (schema, uname, type, pdesc) = desc[p]
            if type == 'procedure' or type == 'function':
                if self._schema is None: sname = name
                else: sname = "%s.%s" % (self._schema, name)
                newproc = procedure(cursor,schema=sname)
                newproc._description = desc[p]
                if self._schema is not None:
                    if inpkg:
                        newproc.__name__ = "%s.%s.%s" % (self._schema, name,
                            uname)
                    else:
                        newproc.__name__ = "%s.%s" % (self._schema, uname)
                elif inpkg:
                    newproc.__name__ = "%s.%s"% (name,uname)
                else:
                    newproc.__name__ = uname
                newproc._exec = 1

                #print "Creating %s object %s (%s)" % (type, uname,
                #    newproc.__name__)

                # Now lets's stuff this name in the connection map
                cursor._mapproc[newproc.__name__] = newproc
                newproc.__doc__ = cursor._connection.decodecdesc(
                    newproc._description, newproc.__name__)
                d[uname] = newproc
            elif type == 'synonym':
               # OK now what?  we need to try to look up the synonym in
               # this proc -- so we have to go back to the top level
               # proc on the cursor (sigh)

               newproc = cursor.findproc(pdesc)
               d[uname] = newproc
        
        d = self.__dict__

        #print "Returning key %s" % name
        if d.has_key(name): return d[name]

        #print "Eek! failed to find %s, schema is %s" % (name, self._schema)
        raise AttributeError, name


    def _argconvertIn(self, ba, argconvert, i):
        """Convert binding array to a more executable form"""
        dty = ba.type()
        if dty == 2:                      # SQLT_NUM
            oba = self._cursor._connection.BindingArray(len(ba),
                 48,'SQLT_STR')
            argconvert[i] = 2
            for i in xrange(len(ba)):
                oba[i] = str(ba[i])
            return oba
        
        return ba

    def _argconvertOut(self, ba, argconvert, i):
        """Un-Convert binding array to a more executable form"""
        cdty = ba.type()
        dty = argconvert[i]

        if dty == 2:                       # Convert back to numeric
            oba = self._cursor._connection.BindingArray(len(ba),
                ba.width(), 'SQLT_NUM')
            force = None
            twopass = 0
            INT = "int"
            LONG = "long"
            FLOAT = "float"
            for i in xrange(len(ba)):
                if ba[i] is None:
                    oba[i] = None
                    continue
                f = float(ba[i])
                l = long(f)
                try:
                    n = int(f)
                except OverflowError:
                    n = 0

                if n == f:
                    oba[i] = n
                    force = INT
                elif l == f:
                    oba[i] = l
                    if force not in (LONG, None): twopass = 1
                    force = LONG 
                else:
                    oba[i] = f
                    if force not in (FLOAT, None): twopass = 1
                    force = FLOAT
            # Try to clean up if we detected a more 'restrictive' type
            if twopass:
                for i in xrange(len(oba)):
                    if oba[i] is None: continue
                    if force is FLOAT:
                        oba[i] = float(ba[i])
                    elif force is LONG:
                        oba[i] = long(ba[i])
            return oba    

        return ba

#
# API 2.0 error classes
#

Error             = dco2.Error
Warning           = dco2.Warning
InterfaceError    = dco2.InterfaceError
DatabaseError     = dco2.DatabaseError
InternalError     = dco2.InternalError
OperationalError  = dco2.OperationalError
ProgrammingError  = dco2.ProgrammingError
IntegrityError    = dco2.IntegrityError
DataError         = dco2.DataError
NotSupportedError = dco2.NotSupportedError

#
# API 2.0 type object constructor horked right from the spec
#

class DBAPITypeObject:
    def __init__(self, *values):
        self.values = []
        # We save the numeric, character, and 'decoded' types
        for v in values:
            self.values.append(v)
            self.values.append(TypeTable(v))
            self.values.append(Type(v))

    def __cmp__(self, other):
        if other in self.values:
            return 0
        if other < self.values:
            return 1
        else:
            return -1
#
# NONAPI Type binding
#

class TypeCoercion:
    def __init__(self, value, type):
        self.value = value
        self.type = type
        self._is_type_coercion_ = 1

    def __repr__(self):
        return repr(self.value)

    def __str__(self):
        return str(self.value)

def isTypeCoercion(ob):
    if type(ob) is not types.InstanceType: return 0
    if issubclass(ob.__class__, TypeCoercion): return 1
    if getattr(ob, '_is_type_coercion_', 0): return 1
    return 0

# Set up the type objects: note that dco2 represents a lot of things as
# strings -- may be required to patch it to use buffers for things (silly)
# to better match the expected types; i.e. if we claim someting is BINARY
# it ought to use the buffer read/write semantics.

# for now we'll be "truthful" and anything dco2 calls a string, we return as
# a string, even though things like 'SQLT_BIN' are pretty clearly meant
# to be binary data.

STRING  =DBAPITypeObject('SQLT_CHR', 'SQLT_STR', 'SQLT_AFC', 'SQLT_BIN', 
                         'SQLT_LNG', 'SQLT_LBI')
BINARY  =DBAPITypeObject('SQLT_BLOB', 'SQLT_CLOB')

NUMBER  =DBAPITypeObject('SQLT_NUM', 'SQLT_INT', 'SQLT_FLT')

DATETIME=DBAPITypeObject('SQLT_DAT')

ROWID   =DBAPITypeObject('SQLT_RDD') # Actually, dco2 calls this a string too


#
# API 2.0 constructor functions 
#

def Date(year, month, day):
    "Date(year, month, day)"
    #t = time.mktime((year, month, day, 0, 0, 0, 0, 0, -1)) 
    #i time.daylight: t = t - time.altzone
    #else: t = t - time.timezone
    return dco2.OracleDate(year, month, day)

def Time(hour, minute, second):
    "Time(hour, minute, second)"
    #t = time.mktime((0, 0, 0, hour, minute, second, 0, 0, -1)) 
    #if time.daylight: t = t - time.altzone
    #else: t = t - time.timezone
    return dco2.OracleDate(0, 0, 0, hour, minute, second)

def Timestamp(year, month, day, hour, minute, second):
    "Timestamp(year, month, day, hour, minut, second)"
    #t = time.mktime((year, month, day, hour, minute, second, 0, 0, -1)) 
    #if time.daylight: t = t - time.altzone
    #else: t = t - time.timezone
    return dco2.OracleDate(year, month, day, hour, minute, second)

def DateFromTicks(ticks):
    "DateFromTicks(ticks)"
    return dco2.OracleDate(ticks)

def TimeFromTicks(ticks):
    "TimeFromTicks(ticks)"
    return dco2.OracleDate(ticks)

def TimestampFromTicks(ticks):
    "TimestampFromTicks(ticks)"
    return dco2.OracleDate(ticks)

def Binary(string):
    return TypeCoercion(string, 'SQLT_LBI') # Make it a long binary

# API 1.0 dbiRaw column
def dbiRaw(string):
    return TypeCoercion(string, 'SQLT_LBI') # Make it a long binary
